<?php
App::uses ( 'AppModel', 'Model' );
/**
 * Sale Model
 */
class Sale extends AppModel {
	public $name = 'Sale';
	public $useTable = 'sale';
	public $belongsTo = array (
			'Customer' => array (
					'className' => 'Customer',
					'foreignKey' => 'customer_id',
					'conditions' => '',
					'fields' => array (
							"Customer.id",
							"Customer.email",
							"Customer.first_name",
							"Customer.last_name" 
					),
					'order' => '' 
			) 
	);
	public $hasMany = array(
			'SaleItem' => array(
					'className' => 'PointOfSale.SaleItem',
					'foreignKey' => 'sale_id',
					//'conditions' => array('SaleItem.layby_id' => '0'),
			)
	);
	
	public function GetData() {
	/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
	* Easy set variables
	*/
	
	/* Array of database columns which should be read and sent back to DataTables. Use a space where
	* you want to insert a non-database field (for example a counter or static image)
	*/
	$aColumns = array( 's.id', 's.created','c.first_name', 's.id', 's.sale_original_price','s.sale_price', 's.customer_id', 'c.last_name', 's.id' );
	
	/* Indexed column (used for fast and accurate table cardinality) */
	$sIndexColumn = "s.id";
	
	/* DB table to use */
	$sTable = "sale s";
	
	$joinTable = "LEFT JOIN customers c ON c.id=s.customer_id";
	
	
	 App::uses('ConnectionManager', 'Model');
     $dataSource = ConnectionManager::getDataSource('default');
         
        /* Database connection information */
        $gaSql['user']       = $dataSource->config['login'];
        $gaSql['password']   = $dataSource->config['password'];
        $gaSql['db']         = $dataSource->config['database'];
        $gaSql['server']     = $dataSource->config['host'];
         
         
        /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
         * If you just want to use the basic configuration for DataTables with PHP server-side, there is
        * no need to edit below this line
        */
         
        /*
         * Local functions
        */
        function fatal_error ( $sErrorMessage = '' )
        {
            header( $_SERVER['SERVER_PROTOCOL'] .' 500 Internal Server Error' );
            die( $sErrorMessage );
        }
         
         
        /*
         * MySQL connection
        */
        if ( ! $gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  ) )
        {
            fatal_error( 'Could not open connection to server' );
        }
         
        if ( ! mysql_select_db( $gaSql['db'], $gaSql['link'] ) )
        {
            fatal_error( 'Could not select database ' );
        }
         
         
        /*
         * Paging
        */
        $sLimit = "";
        if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
        {
            $sLimit = "LIMIT ".intval( $_GET['iDisplayStart'] ).", ".
                    intval( $_GET['iDisplayLength'] );
        }
         
         
        /*
         * Ordering
        */
        $sOrder = "";
        if ( isset( $_GET['iSortCol_0'] ) )
        {
            $sOrder = "ORDER BY  ";
            for ( $i=0 ; $i<intval( $_GET['iSortingCols'] ) ; $i++ )
            {
                if ( $_GET[ 'bSortable_'.intval($_GET['iSortCol_'.$i]) ] == "true" )
                {
                    $sOrder .= "".$aColumns[ intval( $_GET['iSortCol_'.$i] ) ]." ".
                        ($_GET['sSortDir_'.$i]==='asc' ? 'asc' : 'desc') .", ";
                }
            }
         
            $sOrder = substr_replace( $sOrder, "", -2 );
            if ( $sOrder == "ORDER BY" )
            {
                $sOrder = "";
            }
        }
         
         
        /*
         * Filtering
        * NOTE this does not match the built-in DataTables filtering which does it
        * word by word on any field. It's possible to do here, but concerned about efficiency
        * on very large tables, and MySQL's regex functionality is very limited
        */
        $sWhere = "";
        if ( isset($_GET['sSearch']) && $_GET['sSearch'] != "" )
        {
            $sWhere = "WHERE (";
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                $sWhere .= "".$aColumns[$i]." LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ";
            }
            $sWhere = substr_replace( $sWhere, "", -3 );
            $sWhere .= ')';
        }
         
        /* Individual column filtering */
        for ( $i=0 ; $i<count($aColumns) ; $i++ )
        {
            if ( isset($_GET['bSearchable_'.$i]) && $_GET['bSearchable_'.$i] == "true" && $_GET['sSearch_'.$i] != '' )
            {
                if ( $sWhere == "" )

                {
                    $sWhere = "WHERE ";
                }
                else
                {
                    $sWhere .= " AND ";
                }
                $sWhere .= "".$aColumns[$i]." LIKE '%".mysql_real_escape_string($_GET['sSearch_'.$i])."%' ";
            }
        }
         
        /*
         * SQL queries
        * Get data to display
        */
        $sQuery = "
    SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
            FROM   $sTable $joinTable 
            $sWhere
            $sOrder
            $sLimit
            ";
        $rResult = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
         
        /* Data set length after filtering */
        $sQuery = "
    SELECT FOUND_ROWS()
";
        $rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
        $aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
        $iFilteredTotal = $aResultFilterTotal[0];
         		 
        /* Total data set length */
        $sQuery = "
    SELECT COUNT(".$sIndexColumn.")
            FROM   $sTable
            ";
        $rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or fatal_error( 'MySQL Error: ' . mysql_errno() );
        $aResultTotal = mysql_fetch_array($rResultTotal);
        $iTotal = $aResultTotal[0];
         
         
        /*
         * Output
        */
        $output = array(
                "sEcho" => intval($_GET['sEcho']),
                "iTotalRecords" => $iTotal,
                "iTotalDisplayRecords" => $iFilteredTotal,
                "aaData" => array()
        );
		
        while ( $aRow = mysql_fetch_array( $rResult ) )
        {
            $row = array();
            for ( $i=0 ; $i<count($aColumns) ; $i++ )
            {
                if ( $i == "version" )
                {
                    /* Special output formatting for 'version' column */
                    $row[] = ($aRow[ $i ]=="0") ? '-' : $aRow[ $i ];
	
                }
                else if ( $i != ' ' )
                {
                    /* General output */
                    $row[] = $aRow[ $i ];		
                }
				$actionid = $aRow[0];
				
            }
			
		$row['1'] = date('Y-m-d', strtotime($row['1']));	
		$row['2'] = '<a href="Customers/view/'.$row['6'].'">'.$row['2']." ".$row['7'].'</a>';	
		$row['3'] = $this->getItemCount($row['3']);
		$row['4'] = "$".$row['4'] = number_format($row['4'], 2);	
		$row['5'] = "$".$row['5'] = number_format($row['5'], 2);
		$row['6'] = '<a href="Sales/receipt/'.$actionid.'" class="btn">Receipt</a>';
		$row['7'] = '<a href="javascript:;" class="btn" onclick="refundpopup('.$actionid.')">Refund</a>';
		$row['8'] = '<a href="javascript:;" class="btn" onclick="viewmoretable('.$actionid.')">View More</a></tr><tr>';
		
            $output['aaData'][] = $row;
        }
         
        return $output;
    }	
	
	
	function getItemCount($sID)
	{
		App::uses('ConnectionManager', 'Model');
        $dataSource = ConnectionManager::getDataSource('default');
         
        /* Database connection information */
        $gaSql['user']       = $dataSource->config['login'];
        $gaSql['password']   = $dataSource->config['password'];
        $gaSql['db']         = $dataSource->config['database'];
        $gaSql['server']     = $dataSource->config['host'];
 		$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  );

		$sRes = mysql_query("SELECT count(item_id) as count FROM item_sale WHERE sale_id ='".$sID."'", $gaSql['link'] );
		$sRow = mysql_fetch_array( $sRes );
		return $sRow['count'];		
	}
	
	function getItems($itemID)
	{
		App::uses('ConnectionManager', 'Model');
        $dataSource = ConnectionManager::getDataSource('default');
         
        /* Database connection information */
        $gaSql['user']       = $dataSource->config['login'];
        $gaSql['password']   = $dataSource->config['password'];
        $gaSql['db']         = $dataSource->config['database'];
        $gaSql['server']     = $dataSource->config['host'];
 		$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password']  );
          
	
		$itemRes = mysql_query("SELECT *
								FROM consignment_item ci
								LEFT JOIN items i
								ON ci.item_id = i.id
								where ci.consignment_id = '".$itemID."'", $gaSql['link'] );
		$items = '';
		while($itemRow = mysql_fetch_array( $itemRes ))
		{
			$items .= "<a href='items/view/".$itemRow['id']."'>".$itemRow['name']."</a>,";			
		}
		
		$items = rtrim($items,",");
				
		return $items;		
	}
	
	
}